<?php

namespace app\admin\controller;

use app\common\model\Order as OrderModel;
use Env;
use PHPExcel;
use PHPExcel_IOFactory;
use think\Db;
use think\db\Where;
use think\facade\Request;
use think\facade\View;
use think\response\Json;

require_once 'php-barcode.php';

class Order extends Base
{
    //下载当天的订单统计
    public function download(){
        $data = input('get.download');
        if(!$data){
            return "请您选择日期";
        }
        $order_all = model('order')::with('Users')->where('date',$data)->select();
        $objPHPexcel = new PHPExcel();
        $objSheet    = $objPHPexcel->getActiveSheet();//获取当前活动sheet的操作对象
        $objSheet->mergeCells('A1:K1');
        $objSheet->getRowDimension(1)->setRowHeight(25.5);
        $objSheet->setCellValue('A1', '任丘市蔡村周转场医疗废物收集日报表');
        $objSheet->getStyle('A1')->getFont()->setSize(20);
        $objSheet->getStyle('A1')->getFont()->setBold(true);
        $objSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //合并单元格
        $objSheet->mergeCells('H2:K2');
        $objSheet->mergeCells('A3:A4');
        $objSheet->mergeCells('B3:B4');
        $objSheet->mergeCells('I3:I4');
        $objSheet->mergeCells('J3:J4');
        $objSheet->mergeCells('K3:K4');
        $objSheet->mergeCells('C3:H3');
        $objSheet->setCellValue('C3', '医疗废物日产生量（公斤）');
        $objSheet->setCellValue('H2', '交接时间：'.$data);
        $objSheet->setCellValue('A3', '序号');
        $objSheet->getStyle('A3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('A3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objSheet->getStyle('A3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('A3')->getAlignment()->setWrapText(true);
        $objSheet->setCellValue('B3', '医疗机构名称');
        $objSheet->getStyle('B3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('B3')->getAlignment()->setWrapText(true);
        $objSheet->setCellValue('C4', 'HW01 831-001-01感染性废物');
        $objSheet->getStyle('C4')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('C4')->getAlignment()->setWrapText(true);
        $objSheet->setCellValue('D4', 'HW01 831-002-01损伤性废物');
        $objSheet->getStyle('D4')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('D4')->getAlignment()->setWrapText(true);
        $objSheet->setCellValue('E4', 'HW01 831-003-01病理性废物');
        $objSheet->getStyle('E4')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('E4')->getAlignment()->setWrapText(true);
        $objSheet->setCellValue('F4', 'HW01  831-004-01化学性废物');
        $objSheet->getStyle('F4')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('F4')->getAlignment()->setWrapText(true);
        $objSheet->setCellValue('G4', 'HW01 900-001-01为防治动物传染病而需要收集和处置的废物');
        $objSheet->getStyle('G4')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('G4')->getAlignment()->setWrapText(true);
        $objSheet->setCellValue('H4', '合计');
        $objSheet->getStyle('H4')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objSheet->setCellValue('I3', '医废产生单位人员签字');
        $objSheet->getStyle('I3')->getAlignment()->setWrapText(true);
        $objSheet->getStyle('I3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->setCellValue('J3', '周转场交接人员签字');
        $objSheet->getStyle('J3')->getAlignment()->setWrapText(true);
        $objSheet->getStyle('J3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objSheet->setCellValue('K3', '备注');
        $objSheet->getStyle('K3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        // 设置行高

        $objSheet->getRowDimension(4)->setRowHeight(112.5);

        $objSheet->getColumnDimension('A')->setWidth(6.63);
        $objSheet->getColumnDimension('B')->setWidth(8.75);
        $objSheet->getColumnDimension('C')->setWidth(8.63);
        $objSheet->getColumnDimension('D')->setWidth(8.13);
        $objSheet->getColumnDimension('E')->setWidth(8);
        $objSheet->getColumnDimension('F')->setWidth(7.88);
        $objSheet->getColumnDimension('G')->setWidth(8.63);
        $objSheet->getColumnDimension('H')->setWidth(8);
        $objSheet->getColumnDimension('I')->setWidth(7.88);
        $objSheet->getColumnDimension('J')->setWidth(8.5);
        $objSheet->getColumnDimension('K')->setWidth(7.88);

        $j = 5;
        if ($order_all){
            foreach ($order_all as $key => $val) {
                $objSheet->setCellValue('A' . $j, $j-4)
                    ->setCellValue('B' . $j, $order_all[$j - 5]['users']['department'])
                    ->setCellValue('C' . $j, $order_all[$j - 5]['hw_001'])
                    ->setCellValue('D' . $j, $order_all[$j - 5]['hw_002'])
                    ->setCellValue('E' . $j, $order_all[$j - 5]['hw_003'])
                    ->setCellValue('F' . $j, $order_all[$j - 5]['hw_004'])
                    ->setCellValue('G' . $j, $order_all[$j - 5]['hw_900'])
                    ->setCellValue('H' . $j, "=SUM(C$j:G$j)")
                    ->setCellValue('I' . $j, $order_all[$j - 5]['users']['real_name'])
                    ->setCellValue('J' . $j, "马永建")
                    ->setCellValue('K' . $j, $order_all[$j - 5]['remarks']);
                $objSheet->getStyle('A'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('A'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('A'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getStyle('B'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('B'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('B'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getStyle('C'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('C'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('C'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getStyle('D'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('D'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('D'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getStyle('E'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('E'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('E'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getStyle('F'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('F'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('F'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getStyle('G'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('G'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('G'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getStyle('H'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('H'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('H'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getStyle('I'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('I'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('J'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('J'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('J'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getStyle('K'.$j)->getAlignment()->setWrapText(true);
                $objSheet->getStyle('K'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle('K'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objSheet->getRowDimension($j)->setRowHeight(28.5);
                $objSheet->getStyle("B$j")->getAlignment()->setWrapText(true);
                $objSheet->getStyle("B$j")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objSheet->getStyle("B$j")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $j++;
            }
            $objSheet->setCellValue('C'.$j,"=SUM(C5:C$j)")
            ->setCellValue('D'.$j,"=SUM(D5:D$j)")
            ->setCellValue('E'.$j,"=SUM(E5:E$j)")
            ->setCellValue('F'.$j,"=SUM(F5:F$j)")
            ->setCellValue('G'.$j,"=SUM(G5:G$j)")
            ->setCellValue('H'.$j,"=SUM(H5:H$j)");
            $objSheet->getStyle('C'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objSheet->getStyle('C'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objSheet->getStyle('D'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objSheet->getStyle('D'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objSheet->getStyle('E'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objSheet->getStyle('E'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objSheet->getStyle('F'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objSheet->getStyle('F'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objSheet->getStyle('G'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objSheet->getStyle('G'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objSheet->getStyle('H'.$j)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objSheet->getStyle('H'.$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }



        $sheet        = $objPHPexcel->getActiveSheet();
        $sheet->getColumnDimension('A')->setWidth(13.22);
        $objweriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
        $path       = Env::get('root_path') . '/public/static/';
        $objweriter->save($path . '/demo1.xlsx');
        $filename = date('Y-m-d H:i:s', time());
        return download($path . '/demo1.xlsx', "$filename.xlsx");
    }


    //下载所有订单
    public function allDown()
    {
        $start = input('get.star');
        $end   = input('get.end');
        /**
         * 下载所有订单功能！
         */
        $objPHPexcel = new PHPExcel();
        $objSheet    = $objPHPexcel->getActiveSheet();//获取当前活动sheet的操作对象
        $objSheet->setTitle('订单');//设置名称
        $objSheet->setCellValue('A1', '订单号')
            ->setCellValue('B1', '货物名称')
            ->setCellValue('C1', '承运方')
            ->setCellValue('D1', '收货单位')
            ->setCellValue('E1', '合同号')
            ->setCellValue('F1', '请求数量')
            ->setCellValue('G1', '单位')
            ->setCellValue('H1', '验证数量')
            ->setCellValue('I1', '单价/元')
            ->setCellValue('J1', '总价')
            ->setCellValue('K1', '税率')
            ->setCellValue('L1', '备注')
            ->setCellValue('M1', '申请员')
            ->setCellValue('N1', '审核员')
            ->setCellValue('O1', '接收人')
            ->setCellValue('P1', '验证员')
            ->setCellValue('Q1', '验证日期')
            ->setCellValue('R1', '审核意见');
        $order_all = model('order')::with('getPrice,Users,getReceiveUser,getAllowUser')->where('order_number','between',[$start,$end])->where('status','>',4)->select();
//        var_dump([$start,$end],$order_all);die;
//        return json($order_all); die;
        $j = 2;
        foreach ($order_all as $key => $val) {
            $objSheet->setCellValue('A' . $j, $order_all[$j - 2]['order_number'])
                ->setCellValue('B' . $j, $order_all[$j - 2]['goods'])
                ->setCellValue('C' . $j, $order_all[$j - 2]['get_price']['company_name'])
                ->setCellValue('D' . $j, $order_all[$j - 2]['customer_name'])
                ->setCellValue('E' . $j, $order_all[$j - 2]['apply_contract'])
                ->setCellValue('F' . $j, $order_all[$j - 2]['goods_weight'])
                ->setCellValue('G' . $j, $order_all[$j - 2]['goods_attribute'])
                ->setCellValue('H' . $j, $order_all[$j - 2]['receive_num'])
                ->setCellValue('I' . $j, $order_all[$j - 2]['get_price']['price'])
                ->setCellValue('J' . $j, $order_all[$j - 2]['get_price']['end_price'])
                ->setCellValue('K' . $j, $order_all[$j - 2]['get_price']['tax'])
                ->setCellValue('L' . $j, $order_all[$j - 2]['remarks'])
                ->setCellValue('M' . $j, $order_all[$j - 2]['users']['real_name'])
                ->setCellValue('N' . $j, $order_all[$j - 2]['get_receive_user']['real_name'])
                ->setCellValue('O' . $j, $order_all[$j - 2]['get_receive_user']['real_name'])
                ->setCellValue('P' . $j, $order_all[$j - 2]['get_allow_user']['real_name'])
                ->setCellValue('Q' . $j, date("Y-m-d",$order_all[$j - 2]['receive_time']))
                ->setCellValue('R' . $j, $order_all[$j - 2]['status']['text']);
            $j++;
        }
        $sheet        = $objPHPexcel->getActiveSheet();
        $sheet->getColumnDimension('A')->setWidth(13.22);
        $objweriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
        $path       = Env::get('root_path') . '/public/static/';
        $objweriter->save($path . '/demo1.xlsx');
        $filename = date('Y-m-d H:i:s', time());
        return download($path . '/demo1.xlsx', "$filename.xlsx");
    }
    public function details()
    {
        $id           = input('id');
        $order_Info   = model('order')
            ->where('id', $id)
            ->find();
        $price        = Db::name('price')
            ->where('id', $order_Info['price_id'])
            ->find();
        $userInfo     = Db::name('users')
            ->where('id', $order_Info['user_id'])
            ->find();
        $receive_user = Db::name('users')
            ->where('id', $order_Info['receive_user'])
            ->find();
        $allow_user   = Db::name('users')
            ->where('id', $order_Info['allow_user'])
            ->find();
        $viewData     = [
            'order_info'   => $order_Info,
            'price'        => $price,
            'user_info'    => $userInfo,
            'receive_user' => $receive_user,
            'allow_user'   => $allow_user
        ];
        $this->assign($viewData);
        return view();
    }
    public function index()
    {
        //全局查询条件
        $where   = [];
        $keyword = Request::param('keyword');
        if (!empty($keyword)) {
            $where[] = ['goods|order_number', 'like', '%' . $keyword . '%'];
        }
        $dateran = Request::param('dateran');
        if (!empty($dateran)) {
            $getDateran = get_dateran($dateran);
            $where[]    = ['create_time', 'between', $getDateran];
        }
        $list = OrderModel::getList($where, $this->pageSize, ['id' => 'desc']);
        $all_orderNum = model('order')->select();
        $view = [
            'keyword'  => $keyword,
            'dateran'  => $dateran,
            'pageSize' => page_size($this->pageSize, $list->total()),
            'page'     => $list->render(),
            'list'     => $list,
            'empty'    => empty_list(9),
            'all_order_num' =>$all_orderNum
        ];
        View::assign($view);
        return view();
    }
    //删除
    public function del()
    {
        $id = Request::param('id');
        OrderModel::destroy($id);
        return json(['error' => 0, 'msg' => '删除成功']);
    }
    //批量删除
    public function selectDel()
    {
        $id = Request::param('id');
        OrderModel::destroy($id);
        return json(['error' => 0, 'msg' => '删除成功!']);
    }
    public function down()
    {
        $id = Request::param('id');
        $phpexcel = new PHPExcel();
        $phpexcel->setActiveSheetIndex(0);
        $sheet = $phpexcel->getActiveSheet();
        $res = model('order')::with('get_receive_user')
            ->where('id', $id)
            ->find();
        $price = Db::name('price')
            ->where('id', $res['price_id'])
            ->find();
        $userInfo = Db::name('users')
            ->where('id', $res['user_id'])
            ->find();
        $receive_user = Db::name('users')
            ->where('id', $res['receive_user'])
            ->find();
        $allow_user = Db::name('users')
            ->where('id', $res['allow_user'])
            ->find();
        $sheet->setTitle('订单信息');
        /**
         * 设为微软雅黑
         */
        $sheet->getDefaultStyle()->getFont()->setName('微软雅黑');
        /**
         * 合并单元格
         */
        $sheet->mergeCells('A1:K1');
        $sheet->mergeCells('A2:K2');
        $sheet->setCellValue('A1', '洛阳耐火材料有限公司');
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('A2', '发运验收凭据');
        $sheet->getStyle('A2')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('A3', '承运方');
        $sheet->getStyle('A3')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('B3:H3');
        $sheet->setCellValue('B3', $price['company_name']);
        $sheet->getStyle('B3')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('I3', '验证日期');
        $sheet->getStyle('I3')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('J3:K3');
        $sheet->setCellValue('J3', date("Y-m-d", $res['receive_time']));
        $sheet->getStyle('J3')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        /**
         *设置边框
         */
        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),

            ),
        );
        $sheet->getStyle('A3:K6')->applyFromArray($styleThinBlackBorderOutline);

        $sheet->setCellValue('A4', '订单号');
        $sheet->getStyle('A4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('B4', '物资名称');
        $sheet->getStyle('B4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('C4', '收货单位');
        $sheet->getStyle('C4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('D4', '合同号');
        $sheet->getStyle('D4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('E4', '请求数量');
        $sheet->getStyle('E4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('F4', '单位');
        $sheet->getStyle('F4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('G4', '验证数量');
        $sheet->getStyle('G4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('H4', '单价/元');
        $sheet->getStyle('H4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('I4', '总价/元');
        $sheet->getStyle('I4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('J4', '税率');
        $sheet->getStyle('J4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('K4', '备注');
        $sheet->getStyle('K4')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        /**
         * 5 行
         */
        $sheet->setCellValue('A5', $res['order_number']);
        $sheet->getStyle('A5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('B5', $res['goods']);
        $sheet->getStyle('B5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('C5', $res['customer_name']);
        $sheet->getStyle('C5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('C5')->getAlignment()->setWrapText(true);

        $sheet->setCellValue('D5', $res['apply_contract']);
        $sheet->getStyle('D5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('E5', $res['goods_weight']);
        $sheet->getStyle('E5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('F5', $res['goods_attribute']);
        $sheet->getStyle('F5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('G5', $res['receive_num']);
        $sheet->getStyle('G5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('H5', $price['price']);
        $sheet->getStyle('H5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('I5', $res["receive_price"]);
        $sheet->getStyle('I5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('J5', $price['tax']);
        $sheet->getStyle('J5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('K5', $res['remarks']);
        $sheet->getStyle('K5')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('K5')->getAlignment()->setWrapText(true);
        /**
         * 结束5行
         */

        /**
         * 6行开始
         */
        $sheet->setCellValue('A6', '申请员');
        $sheet->getStyle('A6')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('B6', $userInfo['real_name']);
        $sheet->getStyle('B6')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('D6', '审核员');
        $sheet->getStyle('D6')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('E6', $res['get_receive_user']['real_name']);
        $sheet->getStyle('E6')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('G6', '接收人');
        $sheet->getStyle('G6')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('H6', $receive_user['real_name']);
        $sheet->getStyle('H6')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('J6', '验证员');
        $sheet->getStyle('J6')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('K6', $allow_user['real_name']);
        $sheet->getStyle('K6')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        /**
         *重复
         */
        $sheet->mergeCells('A16:K16');
        $sheet->mergeCells('A17:K17');
        $sheet->setCellValue('A16', '洛阳耐火材料有限公司');
        $sheet->getStyle('A16')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('A17', '发运验收凭据');
        $sheet->getStyle('A17')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('A18', '承运方');
        $sheet->getStyle('A18')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('B18:H18');
        $sheet->setCellValue('B18', $price['company_name']);
        $sheet->getStyle('B18')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('I18', '验证日期');
        $sheet->getStyle('I18')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('J18:K18');
        $sheet->setCellValue('J18', date("Y-m-d", $res['receive_time']));
        $sheet->getStyle('J18')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        /**
         *设置边框
         */
        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),

            ),
        );
        $sheet->getStyle('A18:K21')->applyFromArray($styleThinBlackBorderOutline);

        $sheet->setCellValue('A19', '订单号');
        $sheet->getStyle('A19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('B19', '物资名称');
        $sheet->getStyle('B19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('C19', '收货单位');
        $sheet->getStyle('C19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('D19', '合同号');
        $sheet->getStyle('D19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('E19', '请求数量');
        $sheet->getStyle('E19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('F19', '单位');
        $sheet->getStyle('F19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('G19', '验证数量');
        $sheet->getStyle('G19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('H19', '单价/元');
        $sheet->getStyle('H19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('I19', '总价/元');
        $sheet->getStyle('I19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('J19', '税率');
        $sheet->getStyle('J19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('K19', '备注');
        $sheet->getStyle('K19')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//
//
//        /**
//         * 5 行
//         */
        $sheet->setCellValue('A20', $res['order_number']);
        $sheet->getStyle('A20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('B20', $res['goods']);
        $sheet->getStyle('B20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('C20', $res['customer_name']);
        $sheet->getStyle('C20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('C20')->getAlignment()->setWrapText(true);

        $sheet->setCellValue('D20', $res['apply_contract']);
        $sheet->getStyle('D20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('E20', $res['goods_weight']);
        $sheet->getStyle('E20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('F20', '吨');
        $sheet->getStyle('F20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('G20', $res['receive_num']);
        $sheet->getStyle('G20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('H20', $price['price']);
        $sheet->getStyle('H20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('I20', $res["receive_price"]);
        $sheet->getStyle('I20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('J20', $price['tax']);
        $sheet->getStyle('J20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('K20', $res['remarks']);
        $sheet->getStyle('K20')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('K20')->getAlignment()->setWrapText(true);
//        /**
//         * 结束5行
//         */
//
//        /**
//         * 6行开始
//         */
        $sheet->setCellValue('A21', '申请员');
        $sheet->getStyle('A21')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('B21', $userInfo['real_name']);
        $sheet->getStyle('B21')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('D21', '审核员');
        $sheet->getStyle('D21')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('E21', $res['get_receive_user']['real_name']);
        $sheet->getStyle('E21')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('G21', '接收人');
        $sheet->getStyle('G21')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('H21', $receive_user['real_name']);
        $sheet->getStyle('H21')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('J21', '验证员');
        $sheet->getStyle('J21')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->setCellValue('K21', $allow_user['real_name']);
        $sheet->getStyle('K21')->getAlignment()
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        /**
         * 写入条形码
         */
        $im = imagecreatetruecolor(250, 66);
        $black = ImageColorAllocate($im, 0x00, 0x00, 0x00);
        $white = ImageColorAllocate($im, 0xff, 0xff, 0xff);
        imagefilledrectangle($im, 0, 0, 250, 66, $white);
        $data = Barcode::gd($im, $black, 125, 33, 0, "code128", $res['order_number'], 2, 50);
        header('Content-Type: image/gif');
        imagegif($im, $res['order_number'] . '.gif');
        /**
         * 设置图片
         */
        $draw = new \PHPExcel_Worksheet_Drawing();
        $draw->setName('Logo');
        $draw->setDescription('Logo');
        $draw->setPath('./' . $res['order_number'] . '.gif');
        $draw->setCoordinates('G1');
        $draw->setWorksheet($phpexcel->getActiveSheet());
        //去掉第二个条形码
//        $draw16 = new \PHPExcel_Worksheet_Drawing();
//        $draw16->setName('Logo');
//        $draw16->setDescription('Logo');
//        $draw16->setPath('./'.$res['order_number'].'.gif');
//        $draw16->setCoordinates('H16');
//        $draw16->setWorksheet($phpexcel->getActiveSheet());
//        unlink($res['order_number'].'.gif');
        /**
         * end
         */

//        $sheet->getStyle('A3:K6')
//            ->getBorders()
//            ->getAllBorders()
//            ->setBorderStyle(\PHPExcel_Style_Border::BORDER_DOUBLE)
//            ->getBorders()
//            ->getTop()
//            ->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        /**
         * 设置行高
         */
        $sheet->getRowDimension(1)->setRowHeight(54);
        $sheet->getRowDimension(2)->setRowHeight(16.5);
        $sheet->getRowDimension(3)->setRowHeight(16.5);
        $sheet->getRowDimension(4)->setRowHeight(16.5);
        $sheet->getRowDimension(5)->setRowHeight(33);
        $sheet->getRowDimension(6)->setRowHeight(16.5);
        $sheet->getRowDimension(7)->setRowHeight(13.5);
        $sheet->getRowDimension(8)->setRowHeight(13.5);
        $sheet->getRowDimension(9)->setRowHeight(13.5);
        $sheet->getRowDimension(10)->setRowHeight(13.5);
        $sheet->getRowDimension(11)->setRowHeight(13.5);
        $sheet->getRowDimension(12)->setRowHeight(13.5);
        $sheet->getRowDimension(13)->setRowHeight(13.5);
        $sheet->getRowDimension(14)->setRowHeight(13.5);
        $sheet->getRowDimension(15)->setRowHeight(13.5);
        $sheet->getRowDimension(16)->setRowHeight(16.5);
        $sheet->getRowDimension(17)->setRowHeight(16.5);
        $sheet->getRowDimension(18)->setRowHeight(16.5);
        $sheet->getRowDimension(19)->setRowHeight(16.5);
        $sheet->getRowDimension(20)->setRowHeight(33);
        $sheet->getRowDimension(21)->setRowHeight(16.5);
        $sheet->getRowDimension(22)->setRowHeight(16.5);
        $sheet->getRowDimension(23)->setRowHeight(16.5);
        $sheet->getRowDimension(24)->setRowHeight(16.5);
        /**
         * 设置列宽
         */
        $sheet->getColumnDimension('A')->setWidth(13.22);
        $sheet->getColumnDimension('B')->setWidth(10.11);
        $sheet->getColumnDimension('C')->setWidth(9.78);
        $sheet->getColumnDimension('D')->setWidth(14.33);
        $sheet->getColumnDimension('E')->setWidth(7.22);
        $sheet->getColumnDimension('F')->setWidth(3.56);
        $sheet->getColumnDimension('G')->setWidth(8.11);
        $sheet->getColumnDimension('H')->setWidth(6.33);
        $sheet->getColumnDimension('I')->setWidth(9.22);
        $sheet->getColumnDimension('J')->setWidth(4.89);
        $sheet->getColumnDimension('K')->setWidth(10.25);
//        $sheet->setCellValue('A1', '订单编号')
//            ->setCellValue('B1', '商品')
//            ->setCellValue('C1', '客户名称')
//            ->setCellValue('D1', '目的地')
//            ->setCellValue('E1', '申请时间');
//        $sheet->setCellValue('A2', $res['order_number'])
//            ->setCellValue('B2', $res['goods'])
//            ->setCellValue('C2', $res['customer_name'])
//            ->setCellValue('D2', $res['destination'])
//            ->setCellValue('E2', $res['create_time']);
        $objweriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
        $path = Env::get('root_path') . '/public/static/';
        $objweriter->save($path . '/demo1.xls');
        $filename = date('Y-m-d H:i:s', time());
        return '';
    }
}